﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using VideoClassLibrary.Model;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

namespace VideoClassLibrary.BusinessLogicLayer
{
    public class AnnouncementModule
    {
        #region  成员方法
        /// <summary>
        /// 修改公告状态
        /// </summary>
        /// <param name="schoolId">所属学校</param>
        /// <param name="IsPublished">状态</param>
        /// <param name="Announcementid">新闻id</param>
        /// <returns></returns>
        public DataSet UpdateAnnouncementIsPublished(int schoolId, int IsPublished, int Announcementid)
        {
            string sql = "update V_announcement set  IsPublished=@IsPublished where  ID=@Announcementid and schoolid=@schoolId";
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sql.ToString());
            db.AddInParameter(dbCommand, "Announcementid", DbType.Int32, Announcementid);
            db.AddInParameter(dbCommand, "IsPublished", DbType.Boolean, IsPublished);
            db.AddInParameter(dbCommand, "schoolid", DbType.Int32, schoolId);
            DataSet ds = db.ExecuteDataSet(dbCommand);
            return ds;
        }
        /// <summary>
        /// 增加一条公告信息
        /// </summary>
        public int AddAnnouncement(V_announcement model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into V_announcement(");
            strSql.Append("AnnouncementTitle,AnnouncementContent,SchoolId,IsPublished,CreaterId,CreaterName,CreatedTime,UpdateTime)");

            strSql.Append(" values (");
            strSql.Append("@AnnouncementTitle,@AnnouncementContent,@SchoolId,@IsPublished,@CreaterId,@CreaterName,@CreatedTime,@UpdateTime)");
            strSql.Append(";select @@IDENTITY");
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "AnnouncementTitle", DbType.String, model.AnnouncementTitle);
            db.AddInParameter(dbCommand, "AnnouncementContent", DbType.String, model.AnnouncementContent);
            db.AddInParameter(dbCommand, "SchoolId", DbType.Int32, model.SchoolId);
            db.AddInParameter(dbCommand, "IsPublished", DbType.Int32, model.IsPublished);
            db.AddInParameter(dbCommand, "CreaterId", DbType.Int32, model.CreaterId);
            db.AddInParameter(dbCommand, "CreaterName", DbType.String, model.CreaterName);
            db.AddInParameter(dbCommand, "CreatedTime", DbType.DateTime, model.CreatedTime);
            db.AddInParameter(dbCommand, "UpdateTime", DbType.DateTime, model.UpdateTime);
            int result;
            object obj = db.ExecuteScalar(dbCommand);
            if (!int.TryParse(obj.ToString(), out result))
            {
                return 0;
            }
            return result;
        }
        /// <summary>
        /// 更新一条公告信息
        /// </summary>
        public void UpdateAnnouncement(V_announcement model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update V_announcement set ");
            strSql.Append("AnnouncementTitle=@AnnouncementTitle,");
            strSql.Append("AnnouncementContent=@AnnouncementContent,");
            strSql.Append("SchoolId=@SchoolId,");
            strSql.Append("IsPublished=@IsPublished,");
            strSql.Append("CreaterId=@CreaterId,");
            strSql.Append("CreaterName=@CreaterName,");
            strSql.Append("UpdateTime=@UpdateTime");
            strSql.Append(" where Id=@Id ");
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "Id", DbType.Int32, model.Id);
            db.AddInParameter(dbCommand, "AnnouncementTitle", DbType.String, model.AnnouncementTitle);
            db.AddInParameter(dbCommand, "AnnouncementContent", DbType.String, model.AnnouncementContent);
            db.AddInParameter(dbCommand, "SchoolId", DbType.Int32, model.SchoolId);
            db.AddInParameter(dbCommand, "IsPublished", DbType.Int32, model.IsPublished);
            db.AddInParameter(dbCommand, "CreaterId", DbType.Int32, model.CreaterId);
            db.AddInParameter(dbCommand, "CreaterName", DbType.String, model.CreaterName);
            db.AddInParameter(dbCommand, "UpdateTime", DbType.DateTime, model.UpdateTime);
            db.ExecuteNonQuery(dbCommand);

        }

        /// <summary>
        /// 根据公告ID，删除一条公告
        /// </summary>
        public void DeleteAnnouncementById(int Id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from V_announcement ");
            strSql.Append(" where Id=@Id ");
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "Id", DbType.Int32, Id);
            db.ExecuteNonQuery(dbCommand);

        }
        /// <summary>
        /// 查询公告信息
        /// </summary>
        /// <param name="schoolid"></param>
        /// <returns></returns>
        public DataSet QueryAnnouncement( int schoolid,int annid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Id ,AnnouncementTitle,AnnouncementContent,SchoolId,IsPublished,CreaterId,CreaterName,CreatedTime,UpdateTime");
            strSql.Append(" from V_announcement where  schoolid="+schoolid);
            if (annid > 0)
            {
                strSql.AppendLine("  and Id="+annid);
            }
            Database db = VDataBase.CreateDatabase();
            return db.ExecuteDataSet(CommandType.Text,strSql.ToString());

        }
        /// <summary>
        /// 查询公告信息所属状态
        /// </summary>
        /// <param name="schoolid"></param>
        /// <returns></returns>
        public DataSet QueryNotic(int schoolid, int IsPublished)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Id ,AnnouncementTitle,AnnouncementContent,SchoolId,IsPublished,CreaterId,CreaterName,CreatedTime,UpdateTime");
            strSql.Append(" from V_announcement where  schoolid=" + schoolid);

            strSql.Append("  and IsPublished=" + IsPublished);
            strSql.Append(" order by  id desc");
            Database db = VDataBase.CreateDatabase();
            return db.ExecuteDataSet(CommandType.Text, strSql.ToString());

        }
        /// <summary>
        /// 首页查询公告列表，默认5条数据
        /// </summary>
        /// <param name="schoolid"></param>
        /// <returns></returns>
        public DataSet QueryNoticeBySchoolid(string schoolid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("  select top (5) Id ,AnnouncementTitle,AnnouncementContent,SchoolId,IsPublished,CreaterId,CreaterName,CreatedTime,UpdateTime");
            strSql.Append(" from V_announcement ");
            if (schoolid != "")
            {
                strSql.Append(" where schoolid="+schoolid);
            }
            strSql.Append(" order by Id desc ");
            Database db = VDataBase.CreateDatabase();
            return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }



        #endregion  成员方法
    }
}
